package com.chevic.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.sina.sae.util.SaeUserInfo;

public class DBUtil {
	
	/**
	 * 获取主数据连接
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnW() throws Exception{
		return DBUtil.getConnection("main") ;
	}
	
	/**
	 * 获取从数据连接
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnR() throws Exception{
		return DBUtil.getConnection("brother") ;
	}
	
	
	private static Connection getConnection(String type) throws Exception{
		
		//从库
		String URL = "jdbc:mysql://w.rdc.sae.sina.com.cn:3307/app_520i" ;
		
		//主库
		if("main".equals(type)){
			URL="jdbc:mysql://w.rdc.sae.sina.com.cn:3307/app_520i";
		}
		
		// 使用从库读数据
		String sql = "SELECT * FROM app_table";
		// 通过SaeUserInfo提供的静态方法获取应用的access_key和secret_key
		String Username=SaeUserInfo.getAccessKey();
		String Password=SaeUserInfo.getSecretKey();
		String Driver="com.mysql.jdbc.Driver";
		Class.forName(Driver).newInstance();
		Connection con=DriverManager.getConnection(URL,Username,Password);
		return con ;
	}
	
	public static void executeSql(Connection conn,String sql,String[] args) throws Exception{
		PreparedStatement ps = conn.prepareStatement(sql) ;
		for(int i=0; i<args.length ;i++){
			ps.setString(i+1, args[i]) ;
		}
		ps.execute() ;
		ps.close() ;
	}
	
	/**
	 * 
	 * @param conn
	 * @param sql
	 * @param args
	 * @param colNames sql中返回的列名
	 * @return
	 * @throws Exception
	 */
	public List<List<Map<String,String>>> querySel(Connection conn,String sql,String[] args,String[] colNames) throws Exception{
		PreparedStatement ps = conn.prepareStatement(sql) ;
		for(int i=0; i<args.length ;i++){
			ps.setString(i+1, args[i]) ;
		}
		ResultSet rs = ps.executeQuery() ;
		List list = new ArrayList() ;
		while(rs.next()){
			Map map = new HashMap() ;
			for(String s:colNames){
				map.put(s, rs.getString(s)) ;
			}
			list.add(map) ;
		}
		rs.close() ;
		ps.close() ;
		return list ;
	}
	

}
